package org.bigjava.travel.provider;

import org.springframework.stereotype.Component;

/**
 * 车票的SQL语句封装类
 */
@Component
public class CarSqlProvider {

    //查询所有的车票
    public String count(){ return "SELECT count(*) FROM t_car WHERE DELETE_STATUS=0"; }

    //通过ID查询
    public String findById() {
        return "SELECT * FROM t_car WHERE ID = #{id} AND DELETE_STATUS=0";
    }

    //通过ID排序
    public String findList(){
        return "SELECT * FROM t_car WHERE DELETE_STATUS=0 ORDER BY ID ASC";
    }

    //已发布的通过添加时间排序
    public String indexList(){
        return "SELECT * FROM t_car WHERE DELETE_STATUS=0 AND STATE=1 ORDER BY ADD_TIME DESC";
    }

    //通过车票名称模糊查询
    public String findListByQuery(){
        return "SELECT * FROM t_car WHERE DELETE_STATUS=0 AND TITLE LIKE #{query,jdbcType=VARCHAR} ORDER BY ID ASC";
    }

    //修改
    public String update(){
        return "UPDATE t_car SET TITLE=#{title},START_PLACE=#{startPlace},END_PLACE=#{endPlace},START_DATE_AND_TIME=#{startDateAndTime}," +
                "NEED_TIME=#{needTime},GATHER_PLACE=#{gatherPlace},TYPE=#{type},PRICE=#{price},IMG_URL=#{imgUrl},STATE=#{state}," +
                "REMARK=#{remark} WHERE ID=#{id}";
    }

    //保存
    public String save(){
        return "INSERT INTO t_car(ADD_TIME,TITLE,START_PLACE,END_PLACE,START_DATE_AND_TIME," +
                "NEED_TIME,GATHER_PLACE,TYPE,PRICE,IMG_URL,STATE,REMARK) VALUES(NOW(),#{title},#{startPlace}," +
                "#{endPlace},#{startDateAndTime},#{needTime},#{gatherPlace},#{type},#{price},#{imgUrl},#{state},#{remark})";
    }

    //删除
    public String deleteById(){
        return "UPDATE t_cms_car SET DELETE_STATUS=1 WHERE id=#{id}";
    }

    public String state0count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND STATE=0"; }
    public String state1count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND STATE=1"; }
    public String state2count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND STATE=2"; }


    public String type0count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND TYPE=0"; }
    public String type1count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND TYPE=1"; }
    public String type2count(){ return "SELECT count(*) FROM t_cms_car WHERE DELETE_STATUS=0 AND TYPE=2"; }

}
